﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Npgsql;
using Ortodoctor.Componentes;
using System.Web;
using System.Web.Services;

namespace Ortodoctor.modelo
{
    class modeloCliente : conexao
    {
        public modeloCliente()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public int insert(Cliente c)
        {
            int id;
            try
            {
                //con.open();
                String sql = @"INSERT INTO cliente(
                                id_endereco, 
                                nome, 
                                data_nascimento, 
                                cpf, 
                                rg, 
                                telefone1, 
                                telefone2, 
                                email,
                                id_clinica,
                                id_titular) VALUES (
                         :p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9, :p0, :p10) returning id_cliente";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p2", c.Endereco.IdEndereco);
                com.Parameters.AddWithValue("p3", c.Nome);
                com.Parameters.AddWithValue("p4", c.DataNascimento);
                com.Parameters.AddWithValue("p5", c.Cpf);
                com.Parameters.AddWithValue("p6", c.Rg);
                com.Parameters.AddWithValue("p7", c.Telefone1);
                com.Parameters.AddWithValue("p8", c.Telefone2);
                com.Parameters.AddWithValue("p9", c.Email);
                com.Parameters.AddWithValue("p0", c.IdClinica);
                com.Parameters.AddWithValue("p10", c.IdTitular);
                id = (int.Parse(com.ExecuteScalar().ToString()));
            }
            finally
            {
                conn.Close();
            }
            return id;
        }

        public void Log(string operacao, string log)
        {
            DateTime dt = DateTime.Now;
            try
            {
                String sql = @"INSERT INTO cliente_log(operacao, log, data) values (:p1, :p2, :p3)";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", operacao);
                com.Parameters.AddWithValue("p2", log);
                com.Parameters.AddWithValue("p3", DateTime.Now.ToShortDateString() + " - " + DateTime.Now.ToShortTimeString());                
                com.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable getAll()
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = "SELECT * FROM cliente LEFT JOIN endereco using(id_endereco) order by nome";

                if (HttpContext.Current.Session["USUARIO_ACESSO"] != null &&
                    HttpContext.Current.Session["USUARIO_ACESSO"].ToString().CompareTo("2") != 0)
                {
                    sql = "SELECT * FROM cliente INNER JOIN endereco using(id_endereco) where id_clinica=" + HttpContext.Current.Session["ID_CLINICA"] + " order by nome";
                }
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getAllClient()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT C.id_cliente, C.nome, C.telefone1, C.telefone2, C.email, C1.nome as titular FROM cliente C left join cliente C1 on C.id_titular = C1.id_cliente";
                if (HttpContext.Current.Session["USUARIO_ACESSO"].ToString().CompareTo("2") != 0)
                {
                    sql = "SELECT C.id_cliente, C.nome, C.telefone1, C.telefone2, C.email, C1.nome as titular FROM cliente  C left join cliente C1 on C.id_titular = C1.id_cliente where C.id_clinica=" + HttpContext.Current.Session["ID_CLINICA"];
                }
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable ValidaLogin(string nome, string cpf)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT id_cliente, nome FROM cliente where upper(nome)='" + nome.ToUpper() + "' and cpf='" + cpf + "'";

                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public int getMaxId()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT max(id_cliente) as id_cliente FROM cliente";

                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return Convert.ToInt32(dt.Rows[0]["id_cliente"]);
        }

        public DataTable getCliente(int id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT *  FROM cliente INNER JOIN endereco using(id_endereco)" +
                             "WHERE id_cliente=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getClienteIndicator(int id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "select id_cliente_indicou from indicado where id_cliente_indicado = :id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public void update(Cliente c)
        {
            try
            {
                //con.open();
                String sql = @"UPDATE cliente SET nome=:n, data_nascimento=:dn, cpf=:c, id_endereco=:ide, 
                         rg=:r, data_cadastro=:dc, telefone1=:t1, telefone2=:t2, email=:e, id_clinica=:id_clinica, id_usuario_modificacao=:id_modifica, id_titular = :id_titular
                         WHERE id_cliente=:id";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("n", c.Nome);
                com.Parameters.AddWithValue("dn", c.DataNascimento);
                com.Parameters.AddWithValue("c", c.Cpf);
                com.Parameters.AddWithValue("ide", c.Endereco.IdEndereco);
                com.Parameters.AddWithValue("r", c.Rg);
                com.Parameters.AddWithValue("dc", c.DataCadastro);
                com.Parameters.AddWithValue("t1", c.Telefone1);
                com.Parameters.AddWithValue("t2", c.Telefone2);
                com.Parameters.AddWithValue("e", c.Email);
                com.Parameters.AddWithValue("id", c.IdCliente);
                com.Parameters.AddWithValue("id_modifica", HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.Parameters.AddWithValue("id_clinica", c.IdClinica);
                com.Parameters.AddWithValue("id_titular", c.IdTitular);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
        
        public void delete(int id)
        {
            try
            {
                //con.open();
                String sql = "DELETE FROM cliente WHERE id_cliente=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.ExecuteNonQuery();

                sql = "update cliente_hist set id_usuario_modificacao = :id_mod, data_modificacao = :data WHERE id_cliente=:id";
                com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.Parameters.AddWithValue("id_mod", HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.Parameters.AddWithValue("data", DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public String getClientNome(int id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "Select nome from cliente where id_cliente=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally { conn.Close(); }
            return (String)dt.Rows[0][0];
        }

        public String getClientNome(string id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "Select nome from cliente where id_cliente=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally { conn.Close(); }
            return (String)dt.Rows[0][0];
        }

        public bool checkClient(string nome, string cpf)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * from cliente where cpf='" + cpf + "' and nome='" + nome + "'";

                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt.Rows.Count == 0 ? false : true;
        }
    }
}
